package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.erp.ErpGoodsEntity;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.xhs.XhsGoodsEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class XhsGoodsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 拉取商品
     * @param goods
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Long> pullGoods(XhsGoodsEntity goods) {
        Long goodsId = jdbcTemplate.queryForObject("SELECT IFNULL((select id from dc_xhs_goods where spuId=? limit 1),0) id ", Long.class, goods.getSpuId());
        if (goodsId > 0) {

            //更新主表
            jdbcTemplate.update("update dc_xhs_goods set `name`=?,imageUrl=?," +
                            "shortName=?,videoUrl=?,`desc`=?,descImages=? where id=?",
                    goods.getName(),goods.getImageUrl(),goods.getShortName(),goods.getVideoUrl(),goods.getDesc(),goods.getDescImages(),goodsId);

            //查询子表是否存在
            Long specId= jdbcTemplate.queryForObject("SELECT IFNULL((select id from dc_xhs_goods_spec where spuId=? and skuId=? limit 1),0) id ", Long.class, goods.getSpuId(),goods.getSkuId());
            if(specId>0){
                //存在，更新
                jdbcTemplate.update("update dc_xhs_goods_spec set price=?,stock=?,spec=?  where id=?",
                        goods.getPrice(),goods.getStock(),goods.getSpec(),specId);
            }else{
                //不存在，新增
                jdbcTemplate.update("INSERT INTO dc_xhs_goods_spec (goodsId,skuId,spuId,price,stock,erpCode,spec) VALUE (?,?,?,?,?,?,?)"
                ,goodsId,goods.getSkuId(),goods.getSpuId(),goods.getPrice(),goods.getStock(),goods.getErpCode(),goods.getSpec());
            }

            return new ResultVo<>(EnumResultVo.DataExist, "已存在，更新状态",goodsId);
        }

        /*****1、添加order*****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_xhs_goods");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" `name`=?,");
        orderInsertSQL.append(" goodsNum=?,");
        orderInsertSQL.append(" imageUrl=?,");
        orderInsertSQL.append(" shortName=?,");
        orderInsertSQL.append(" spuId=?,");
        orderInsertSQL.append(" videoUrl=?,");
        orderInsertSQL.append(" `desc`=?,");
        orderInsertSQL.append(" descImages=?,");
        orderInsertSQL.append(" shopId=?");
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, goods.getName());
                    ps.setString(2, goods.getGoodsNum());
                    ps.setString(3 ,goods.getImageUrl());
                    ps.setString(4 ,goods.getShortName());
                    ps.setString(5 ,goods.getSpuId());
                    ps.setString(6 ,goods.getVideoUrl());
                    ps.setString(7 ,goods.getDesc());
                    ps.setString(8,goods.getDescImages());
                    ps.setInt(9,goods.getShopId());

                    return ps;
                }
            }, keyHolder);

            Long orderIdNew = keyHolder.getKey().longValue();
            //新增子表
            jdbcTemplate.update("INSERT INTO dc_xhs_goods_spec (goodsId,skuId,spuId,price,stock,erpCode,spec) VALUE (?,?,?,?,?,?,?)"
                    ,orderIdNew,goods.getSkuId(),goods.getSpuId(),goods.getPrice(),goods.getStock(),goods.getErpCode(),goods.getSpec());

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功",orderIdNew);
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
        }
    }

    /**
     * 获取商品列表（按SKU）
     * @param shopId
     * @param pageIndex
     * @param pageSize
     * @param goodsNum
     * @return
     */
    @Transactional
    public PagingResponse<XhsGoodsEntity> getGoodsList(Integer shopId, Integer pageIndex, Integer pageSize, String goodsNum) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" g.shopId,g.name,g.goodsNum,g.imageUrl,g.shortName,g.spuId,g.videoUrl,g.desc,g.descImages ");
        sql.append(",gs.id,gs.skuId,gs.erpCode,gs.price,gs.stock,gs.spec,gs.erpSkuId ");
        sql.append(" FROM dc_xhs_goods_spec AS gs ");
        sql.append(" LEFT JOIN dc_xhs_goods as g on g.id = gs.goodsId ");
        sql.append(" WHERE g.shopId = ? ");

        List<Object> params = new ArrayList<>();
        params.add(shopId);
        if (!StringUtils.isEmpty(goodsNum)) {
            sql.append(" AND (g.goodsNum = ? OR g.spuId=? OR gs.erpCode=? )");
            params.add(goodsNum);
            params.add(goodsNum);
            params.add(goodsNum);
        }

        sql.append("ORDER BY g.modifyOn DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<XhsGoodsEntity> lists = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(XhsGoodsEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 关联ERP系统商品规格
     * @param xhsGoodsSpecId
     * @param erpCode
     * @return
     */
    @Transactional
    public ResultVo<Integer> linkErpGoodsSpec(Long xhsGoodsSpecId,String erpCode){
        var xhsGoodsId = jdbcTemplate.queryForObject("SELECT goodsId FROM dc_xhs_goods_spec where id = ? ",Long.class,xhsGoodsSpecId);
        ErpGoodsSpecEntity erpGoodsSpecEntity = null;
        String goodsNum = "";
        try {
            //查询erp系统goods_spec
            String sql = " SELECT id,goodsId FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? ";
            erpGoodsSpecEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), erpCode);
            var goods = jdbcTemplate.queryForObject("SELECT number FROM "+Tables.ErpGoods+" WHERE id=?",new BeanPropertyRowMapper<>(ErpGoodsEntity.class),erpGoodsSpecEntity.getGoodsId());
            goodsNum = goods.getNumber();
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.DataExist, "ERP系统找不到商品规格数据："+erpCode);
        }

        jdbcTemplate.update("UPDATE dc_xhs_goods_spec SET erpSkuId=?,erpGoodsId=?,erpCode=? WHERE id=?",erpGoodsSpecEntity.getId(),erpGoodsSpecEntity.getGoodsId(),erpCode,xhsGoodsSpecId);
        jdbcTemplate.update("UPDATE dc_xhs_goods SET goodsNum=? WHERE id=? ",goodsNum,xhsGoodsId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS",erpGoodsSpecEntity.getId());
    }


    public XhsGoodsEntity getGoodsSpecByXhsSkuId(String xhsSkuId) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT SQL_CALC_FOUND_ROWS ");
            sql.append(" g.shopId,g.name,g.goodsNum,g.imageUrl,g.shortName,g.spuId,g.videoUrl,g.desc,g.descImages ");
            sql.append(",gs.id,gs.skuId,gs.erpCode,gs.price,gs.stock,gs.spec,gs.erpSkuId ");
            sql.append(" FROM dc_xhs_goods_spec AS gs ");
            sql.append(" LEFT JOIN dc_xhs_goods as g on g.id = gs.goodsId ");
            sql.append(" WHERE gs.skuId = ? ");

            return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(XhsGoodsEntity.class), xhsSkuId);
        } catch (Exception e) {
            return null;
        }
    }

}
